package ca.llsutherland.nfl.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ca.llsutherland.nfl.exception.ValidationException;
import ca.llsutherland.nfl.persistence.DatabaseConnectionManager;
import ca.llsutherland.nfl.util.StringUtil;


public abstract class BaseDao {
	 protected Long create(String sql) {
//		 	System.out.println(sql);
	        Statement statement = null;
	        ResultSet rs = null;
	        try {
	            statement = DatabaseConnectionManager.getInstance().getDatabaseConnection().getCurrentConnection().createStatement();
	            int rowAffected = -1;
	            statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
	            
	            rs = statement.getGeneratedKeys();
	            if(rs.next()){
	            	rowAffected = rs.getInt(1);
	            }
	            rs.close();
	            
	            Long rowAffectedAsLong = Long.valueOf(rowAffected);
//	            System.out.print(" : " + rowAffected);
				DatabaseConnectionManager.getInstance().registerNewDomainObject(rowAffectedAsLong, getUnderlyingTableName());
	            return rowAffectedAsLong;
	        } catch (SQLException e) {
	            throw new ValidationException(e.getMessage() + " : " + sql);
	        } finally {
	            statement = null;
	            rs = null;
	        }
	    }

	    protected void delete(String sql, Long idToRemove) throws ValidationException {
	        Statement statement = null;
	        try {
	            statement = DatabaseConnectionManager.getInstance().getDatabaseConnection().getCurrentConnection().createStatement();
	            int rowsAffected = statement.executeUpdate(sql);
	            if(rowsAffected < 1) {
	                DatabaseConnectionManager.getInstance().removeDomainObject(idToRemove);
	                throw new ValidationException("Database Delete failed: " + sql);
	            }
	        } catch (SQLException e) {
	            throw new ValidationException(e.getMessage() + " : " + sql);
	        } finally {
	            statement = null;
	        }
	    }

	    protected ResultSet executeStoredProc(String sql) {
	        CallableStatement statement = null;
	        try {
	            statement = DatabaseConnectionManager.getInstance().getDatabaseConnection().getCurrentConnection().prepareCall(sql);
	            return statement.executeQuery(sql);
	        } catch (SQLException e) {
	            throw new ValidationException(e.getMessage() + " : " + sql);
	        } finally {
	            statement = null;
	        }
	    }

	    protected ResultSet findByQuery(String sql) {
	        Statement statement = null;
	        try {
	            statement = DatabaseConnectionManager.getInstance().getDatabaseConnection().getCurrentConnection().createStatement();
	            return statement.executeQuery(sql);
	        } catch (SQLException e) {
	            throw new ValidationException(e.getMessage() + " : " + sql);
	        } finally {
	            statement = null;

	        }
	    }

	    protected Long findLastInsertedId() {
	        int id = -1;
	        
	        String sql = "SELECT LAST_INSERT_ID()";
	        ResultSet rs = findByQuery(sql);
	        try {
	            if (rs.next()) {
	                id = rs.getInt(1);
	            }
	            rs.close();
	            return Long.valueOf(id);
	        } catch (SQLException e) {
	            throw new ValidationException(e.getMessage() + " : " + sql);
	        } finally {
	            rs = null;
	        }
	    }

	    public Long update(String sql) {
	        Statement statement = null;
	        try {
	            statement = DatabaseConnectionManager.getInstance().getDatabaseConnection().getCurrentConnection().createStatement();
	            int rowsAffected = statement.executeUpdate(sql);
	            if(rowsAffected < 1) {
	                throw new ValidationException("Database Updated failed: " + sql);
	            }
	            return findLastInsertedId();
	        } catch (SQLException e) {
	            throw new ValidationException(e.getMessage() + " : " + sql);
	        } finally {
	            statement = null;
	        }
	    }
	    
	    public abstract String getUnderlyingTableName();

	    protected String getFindByIdQuery(Long id) {
	        return "SELECT * FROM " + getUnderlyingTableName() + " WHERE ID = " + id + ";";
	    }
	    
	    protected String getFindByNameQuery(String name) {
	        return "SELECT * FROM " + getUnderlyingTableName() + " WHERE NAME = " + StringUtil.addSingleQuotes(name) + ";";
	    }
}
